package com.example.demo.dao;
import com.example.demo.entity.OrdersVo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.ArrayList;

public class OrdersDao {

    public OrdersVo findOrdersId(Integer id) throws Exception {
        OrdersVo o = null;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con = DriverManager.getConnection(DB.JDBC_URL, DB.JDBC_USER, DB.JDBC_PASSWORD);
            ps = con.prepareStatement("select * from Orders where OrdersID = ?");
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                o = new OrdersVo();
                o.setOrdersid(rs.getInt("OrdersID"));
                o.setUserid(rs.getInt("UserID"));
                o.setOrdersstatus(rs.getString("OrderStatus"));
                o.setTotalprice(rs.getInt("TotalPrice"));
            }
        }catch (Exception e){
            e.printStackTrace();
            throw new Exception("查询订单失败");
        }finally {
            if (rs!=null) rs.close();
            if (ps!=null) ps.close();
            if (con!=null) con.close();
        }
        return o;
    }

    public List<OrdersVo> findOrders(String address) throws Exception {
        List<OrdersVo> orders = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con = DriverManager.getConnection(DB.JDBC_URL, DB.JDBC_USER, DB.JDBC_PASSWORD);
            ps = con.prepareStatement("select * from Orders where OrderStatus = ?");
            ps.setString(1, address);
            rs = ps.executeQuery();
            while (rs.next()){
                OrdersVo o=new OrdersVo();
                o.setOrdersid(rs.getInt("OrdersID"));
                o.setUserid(rs.getInt("UserID"));
                o.setOrdersstatus(rs.getString("OrderStatus"));
                o.setTotalprice(rs.getInt("TotalPrice"));
                orders.add(o);
            }
        }catch (Exception e){
            e.printStackTrace();
            throw new Exception("查询订单失败");
        }finally {
            if (rs !=null) rs.close();
            if (ps !=null) ps.close();
            if (con !=null) con.close();
        }
        return orders;
    }

    public void saveOrders(OrdersVo o) throws Exception {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con = DriverManager.getConnection(DB.JDBC_URL, DB.JDBC_USER, DB.JDBC_PASSWORD);
            ps = con.prepareStatement("insert into Orders(UserID,OrderStatus,TotalPrice) values(?,?,?)");

            ps.setInt(1,o.getUserid());
            ps.setString(2, o.getOrdersstatus());
            ps.setDouble(3,o.getTotalprice());
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            throw new Exception("保存订单失败");
        }finally {
            if (ps != null) ps.close();
            if (con != null) con.close();

        }
    }

    public void updateOrder(OrdersVo o)throws Exception{
        Connection con = null;
        PreparedStatement ps = null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con = java.sql.DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
            ps=con.prepareStatement("update Orders set UserID=?,OrderStatus=? ,TotalPrice=?  where OrdersID=?");

            ps.setInt(1,o.getUserid());
            ps.setString(2,o.getOrdersstatus());
            ps.setDouble(3,o.getTotalprice());
            ps.setInt(4,o.getOrdersid());
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            throw new Exception("更新订单失败");
        }finally {
            if (ps != null) ps.close();
            if (con != null) con.close();

        }
    }

    public void deleteOrderById(Integer id) throws Exception {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con = DriverManager.getConnection(DB.JDBC_URL, DB.JDBC_USER, DB.JDBC_PASSWORD);
            ps=con.prepareStatement("delete from Orders where OrdersID = ?");
            ps.setInt(1,id);
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            throw new Exception("删除订单失败");
        }finally {
            if (ps != null) ps.close();
            if (con != null) con.close();
        }
    }
}
